{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# More JOIN operations\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/1/10/Movie-er.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 1962 movies\n",
    "\n",
    "List the films where the **yr** is 1962 [Show **id, title**]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "movie <- dbReadTable(con, 'movie')\n",
    "casting <- dbReadTable(con, 'casting')\n",
    "actor <- dbReadTable(con, 'actor')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 86 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th><th scope=col>title</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>10212</td><td>A Kind of Loving                           </td></tr>\n",
       "\t<tr><td>10329</td><td>A Symposium on Popular Songs               </td></tr>\n",
       "\t<tr><td>10347</td><td>A Very Private Affair (Vie PrivÃ©e)        </td></tr>\n",
       "\t<tr><td>10648</td><td>An Autumn Afternoon                        </td></tr>\n",
       "\t<tr><td>10868</td><td>Atraco a las tres                          </td></tr>\n",
       "\t<tr><td>11006</td><td>Barabbas                                   </td></tr>\n",
       "\t<tr><td>11053</td><td>Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)</td></tr>\n",
       "\t<tr><td>11199</td><td>Big and Little Wong Tin Bar                </td></tr>\n",
       "\t<tr><td>11230</td><td>Billy Budd                                 </td></tr>\n",
       "\t<tr><td>11234</td><td>Billy Rose's Jumbo                         </td></tr>\n",
       "\t<tr><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>20161</td><td>The Premature Burial            </td></tr>\n",
       "\t<tr><td>20730</td><td>The Trial                       </td></tr>\n",
       "\t<tr><td>21051</td><td>Through a Glass Darkly          </td></tr>\n",
       "\t<tr><td>21110</td><td>To Kill a Mockingbird           </td></tr>\n",
       "\t<tr><td>21189</td><td>Tower of London                 </td></tr>\n",
       "\t<tr><td>21324</td><td>Two Half Times in Hell          </td></tr>\n",
       "\t<tr><td>21462</td><td>Varan the Unbelievable          </td></tr>\n",
       "\t<tr><td>21494</td><td>Village of Daughters            </td></tr>\n",
       "\t<tr><td>21673</td><td>What Ever Happened to Baby Jane?</td></tr>\n",
       "\t<tr><td>21761</td><td>Who's Got the Action?           </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 86 × 2\n",
       "\\begin{tabular}{ll}\n",
       " id & title\\\\\n",
       " <int> & <chr>\\\\\n",
       "\\hline\n",
       "\t 10212 & A Kind of Loving                           \\\\\n",
       "\t 10329 & A Symposium on Popular Songs               \\\\\n",
       "\t 10347 & A Very Private Affair (Vie PrivÃ©e)        \\\\\n",
       "\t 10648 & An Autumn Afternoon                        \\\\\n",
       "\t 10868 & Atraco a las tres                          \\\\\n",
       "\t 11006 & Barabbas                                   \\\\\n",
       "\t 11053 & Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)\\\\\n",
       "\t 11199 & Big and Little Wong Tin Bar                \\\\\n",
       "\t 11230 & Billy Budd                                 \\\\\n",
       "\t 11234 & Billy Rose's Jumbo                         \\\\\n",
       "\t ... & ...\\\\\n",
       "\t 20161 & The Premature Burial            \\\\\n",
       "\t 20730 & The Trial                       \\\\\n",
       "\t 21051 & Through a Glass Darkly          \\\\\n",
       "\t 21110 & To Kill a Mockingbird           \\\\\n",
       "\t 21189 & Tower of London                 \\\\\n",
       "\t 21324 & Two Half Times in Hell          \\\\\n",
       "\t 21462 & Varan the Unbelievable          \\\\\n",
       "\t 21494 & Village of Daughters            \\\\\n",
       "\t 21673 & What Ever Happened to Baby Jane?\\\\\n",
       "\t 21761 & Who's Got the Action?           \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 86 × 2\n",
       "\n",
       "| id &lt;int&gt; | title &lt;chr&gt; |\n",
       "|---|---|\n",
       "| 10212 | A Kind of Loving                            |\n",
       "| 10329 | A Symposium on Popular Songs                |\n",
       "| 10347 | A Very Private Affair (Vie PrivÃ©e)         |\n",
       "| 10648 | An Autumn Afternoon                         |\n",
       "| 10868 | Atraco a las tres                           |\n",
       "| 11006 | Barabbas                                    |\n",
       "| 11053 | Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚) |\n",
       "| 11199 | Big and Little Wong Tin Bar                 |\n",
       "| 11230 | Billy Budd                                  |\n",
       "| 11234 | Billy Rose's Jumbo                          |\n",
       "| ... | ... |\n",
       "| 20161 | The Premature Burial             |\n",
       "| 20730 | The Trial                        |\n",
       "| 21051 | Through a Glass Darkly           |\n",
       "| 21110 | To Kill a Mockingbird            |\n",
       "| 21189 | Tower of London                  |\n",
       "| 21324 | Two Half Times in Hell           |\n",
       "| 21462 | Varan the Unbelievable           |\n",
       "| 21494 | Village of Daughters             |\n",
       "| 21673 | What Ever Happened to Baby Jane? |\n",
       "| 21761 | Who's Got the Action?            |\n",
       "\n"
      ],
      "text/plain": [
       "    id    title                                      \n",
       "1   10212 A Kind of Loving                           \n",
       "2   10329 A Symposium on Popular Songs               \n",
       "3   10347 A Very Private Affair (Vie PrivÃ©e)        \n",
       "4   10648 An Autumn Afternoon                        \n",
       "5   10868 Atraco a las tres                          \n",
       "6   11006 Barabbas                                   \n",
       "7   11053 Battle Beyond the Sun (Ð\\235ÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)\n",
       "8   11199 Big and Little Wong Tin Bar                \n",
       "9   11230 Billy Budd                                 \n",
       "10  11234 Billy Rose's Jumbo                         \n",
       "... ...   ...                                        \n",
       "77  20161 The Premature Burial                       \n",
       "78  20730 The Trial                                  \n",
       "79  21051 Through a Glass Darkly                     \n",
       "80  21110 To Kill a Mockingbird                      \n",
       "81  21189 Tower of London                            \n",
       "82  21324 Two Half Times in Hell                     \n",
       "83  21462 Varan the Unbelievable                     \n",
       "84  21494 Village of Daughters                       \n",
       "85  21673 What Ever Happened to Baby Jane?           \n",
       "86  21761 Who's Got the Action?                      "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "movie %>% filter(yr==1962) %>% select(id, title)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. When was Citizen Kane released?\n",
    "\n",
    "Give year of 'Citizen Kane'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1941</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " yr\\\\\n",
       " <int>\\\\\n",
       "\\hline\n",
       "\t 1941\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| yr &lt;int&gt; |\n",
       "|---|\n",
       "| 1941 |\n",
       "\n"
      ],
      "text/plain": [
       "  yr  \n",
       "1 1941"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "movie %>% \n",
    "    filter(title=='Citizen Kane') \n",
    "    %>% select(yr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Star Trek movies\n",
    "\n",
    "List all of the Star Trek movies, include the **id**, **title** and **yr** (all of these movies include the words Star Trek in the title). Order results by year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 11 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th><th scope=col>title</th><th scope=col>yr</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>17772</td><td>Star Trek: The Motion Picture         </td><td>1979</td></tr>\n",
       "\t<tr><td>17775</td><td>Star Trek II: The Wrath of Khan       </td><td>1982</td></tr>\n",
       "\t<tr><td>17776</td><td>Star Trek III: The Search for Spock   </td><td>1984</td></tr>\n",
       "\t<tr><td>17777</td><td>Star Trek IV: The Voyage Home         </td><td>1986</td></tr>\n",
       "\t<tr><td>17779</td><td>Star Trek V: The Final Frontier       </td><td>1989</td></tr>\n",
       "\t<tr><td>17780</td><td>Star Trek VI: The Undiscovered Country</td><td>1991</td></tr>\n",
       "\t<tr><td>17774</td><td>Star Trek Generations                 </td><td>1994</td></tr>\n",
       "\t<tr><td>17770</td><td>Star Trek: First Contact              </td><td>1996</td></tr>\n",
       "\t<tr><td>17771</td><td>Star Trek: Insurrection               </td><td>1998</td></tr>\n",
       "\t<tr><td>17778</td><td>Star Trek Nemesis                     </td><td>2002</td></tr>\n",
       "\t<tr><td>17773</td><td>Star Trek                             </td><td>2009</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 11 × 3\n",
       "\\begin{tabular}{lll}\n",
       " id & title & yr\\\\\n",
       " <int> & <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t 17772 & Star Trek: The Motion Picture          & 1979\\\\\n",
       "\t 17775 & Star Trek II: The Wrath of Khan        & 1982\\\\\n",
       "\t 17776 & Star Trek III: The Search for Spock    & 1984\\\\\n",
       "\t 17777 & Star Trek IV: The Voyage Home          & 1986\\\\\n",
       "\t 17779 & Star Trek V: The Final Frontier        & 1989\\\\\n",
       "\t 17780 & Star Trek VI: The Undiscovered Country & 1991\\\\\n",
       "\t 17774 & Star Trek Generations                  & 1994\\\\\n",
       "\t 17770 & Star Trek: First Contact               & 1996\\\\\n",
       "\t 17771 & Star Trek: Insurrection                & 1998\\\\\n",
       "\t 17778 & Star Trek Nemesis                      & 2002\\\\\n",
       "\t 17773 & Star Trek                              & 2009\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 11 × 3\n",
       "\n",
       "| id &lt;int&gt; | title &lt;chr&gt; | yr &lt;int&gt; |\n",
       "|---|---|---|\n",
       "| 17772 | Star Trek: The Motion Picture          | 1979 |\n",
       "| 17775 | Star Trek II: The Wrath of Khan        | 1982 |\n",
       "| 17776 | Star Trek III: The Search for Spock    | 1984 |\n",
       "| 17777 | Star Trek IV: The Voyage Home          | 1986 |\n",
       "| 17779 | Star Trek V: The Final Frontier        | 1989 |\n",
       "| 17780 | Star Trek VI: The Undiscovered Country | 1991 |\n",
       "| 17774 | Star Trek Generations                  | 1994 |\n",
       "| 17770 | Star Trek: First Contact               | 1996 |\n",
       "| 17771 | Star Trek: Insurrection                | 1998 |\n",
       "| 17778 | Star Trek Nemesis                      | 2002 |\n",
       "| 17773 | Star Trek                              | 2009 |\n",
       "\n"
      ],
      "text/plain": [
       "   id    title                                  yr  \n",
       "1  17772 Star Trek: The Motion Picture          1979\n",
       "2  17775 Star Trek II: The Wrath of Khan        1982\n",
       "3  17776 Star Trek III: The Search for Spock    1984\n",
       "4  17777 Star Trek IV: The Voyage Home          1986\n",
       "5  17779 Star Trek V: The Final Frontier        1989\n",
       "6  17780 Star Trek VI: The Undiscovered Country 1991\n",
       "7  17774 Star Trek Generations                  1994\n",
       "8  17770 Star Trek: First Contact               1996\n",
       "9  17771 Star Trek: Insurrection                1998\n",
       "10 17778 Star Trek Nemesis                      2002\n",
       "11 17773 Star Trek                              2009"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "movie %>% \n",
    "    filter(str_detect(title, 'Star Trek')) %>% \n",
    "    select(id, title, yr) %>% \n",
    "    arrange(yr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. id for actor Glenn Close\n",
    "\n",
    "What **id** number does the actor 'Glenn Close' have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>140</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " id\\\\\n",
       " <int>\\\\\n",
       "\\hline\n",
       "\t 140\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| id &lt;int&gt; |\n",
       "|---|\n",
       "| 140 |\n",
       "\n"
      ],
      "text/plain": [
       "  id \n",
       "1 140"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "actor %>%\n",
    "    filter(name=='Glenn Close') %>%\n",
    "    select(id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. id for Casablanca\n",
    "\n",
    "What is the **id** of the film 'Casablanca'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>11768</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " id\\\\\n",
       " <int>\\\\\n",
       "\\hline\n",
       "\t 11768\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| id &lt;int&gt; |\n",
       "|---|\n",
       "| 11768 |\n",
       "\n"
      ],
      "text/plain": [
       "  id   \n",
       "1 11768"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "movie %>% \n",
    "    filter(title=='Casablanca') %>% \n",
    "    select(id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Cast list for Casablanca\n",
    "\n",
    "Obtain the cast list for 'Casablanca'.\n",
    "\n",
    "> _what is a cast list?_  \n",
    "> The cast list is the names of the actors who were in the movie.\n",
    "\n",
    "Use **movieid=11768**, (or whatever value you got from the previous question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 32 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Peter Lorre     </td></tr>\n",
       "\t<tr><td>John Qualen     </td></tr>\n",
       "\t<tr><td>Madeleine LeBeau</td></tr>\n",
       "\t<tr><td>Jack Benny      </td></tr>\n",
       "\t<tr><td>Dan Seymour     </td></tr>\n",
       "\t<tr><td>Norma Varden    </td></tr>\n",
       "\t<tr><td>Ingrid Bergman  </td></tr>\n",
       "\t<tr><td>Conrad Veidt    </td></tr>\n",
       "\t<tr><td>Leon Belasco    </td></tr>\n",
       "\t<tr><td>Humphrey Bogart </td></tr>\n",
       "\t<tr><td>...</td></tr>\n",
       "\t<tr><td>William Edmunds </td></tr>\n",
       "\t<tr><td>Gregory Gaye    </td></tr>\n",
       "\t<tr><td>Torben Meyer    </td></tr>\n",
       "\t<tr><td>Georges Renavent</td></tr>\n",
       "\t<tr><td>Jean Del Val    </td></tr>\n",
       "\t<tr><td>Louis V. Arco   </td></tr>\n",
       "\t<tr><td>Trude Berliner  </td></tr>\n",
       "\t<tr><td>Ilka GrÃ¼nig    </td></tr>\n",
       "\t<tr><td>Richard Ryen    </td></tr>\n",
       "\t<tr><td>Hans Twardowski </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 32 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Peter Lorre     \\\\\n",
       "\t John Qualen     \\\\\n",
       "\t Madeleine LeBeau\\\\\n",
       "\t Jack Benny      \\\\\n",
       "\t Dan Seymour     \\\\\n",
       "\t Norma Varden    \\\\\n",
       "\t Ingrid Bergman  \\\\\n",
       "\t Conrad Veidt    \\\\\n",
       "\t Leon Belasco    \\\\\n",
       "\t Humphrey Bogart \\\\\n",
       "\t ...\\\\\n",
       "\t William Edmunds \\\\\n",
       "\t Gregory Gaye    \\\\\n",
       "\t Torben Meyer    \\\\\n",
       "\t Georges Renavent\\\\\n",
       "\t Jean Del Val    \\\\\n",
       "\t Louis V. Arco   \\\\\n",
       "\t Trude Berliner  \\\\\n",
       "\t Ilka GrÃ¼nig    \\\\\n",
       "\t Richard Ryen    \\\\\n",
       "\t Hans Twardowski \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 32 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Peter Lorre      |\n",
       "| John Qualen      |\n",
       "| Madeleine LeBeau |\n",
       "| Jack Benny       |\n",
       "| Dan Seymour      |\n",
       "| Norma Varden     |\n",
       "| Ingrid Bergman   |\n",
       "| Conrad Veidt     |\n",
       "| Leon Belasco     |\n",
       "| Humphrey Bogart  |\n",
       "| ... |\n",
       "| William Edmunds  |\n",
       "| Gregory Gaye     |\n",
       "| Torben Meyer     |\n",
       "| Georges Renavent |\n",
       "| Jean Del Val     |\n",
       "| Louis V. Arco    |\n",
       "| Trude Berliner   |\n",
       "| Ilka GrÃ¼nig     |\n",
       "| Richard Ryen     |\n",
       "| Hans Twardowski  |\n",
       "\n"
      ],
      "text/plain": [
       "    name            \n",
       "1   Peter Lorre     \n",
       "2   John Qualen     \n",
       "3   Madeleine LeBeau\n",
       "4   Jack Benny      \n",
       "5   Dan Seymour     \n",
       "6   Norma Varden    \n",
       "7   Ingrid Bergman  \n",
       "8   Conrad Veidt    \n",
       "9   Leon Belasco    \n",
       "10  Humphrey Bogart \n",
       "... ...             \n",
       "23  William Edmunds \n",
       "24  Gregory Gaye    \n",
       "25  Torben Meyer    \n",
       "26  Georges Renavent\n",
       "27  Jean Del Val    \n",
       "28  Louis V. Arco   \n",
       "29  Trude Berliner  \n",
       "30  Ilka GrÃ¼nig    \n",
       "31  Richard Ryen    \n",
       "32  Hans Twardowski "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "casting %>% \n",
    "    filter(movieid==11768) %>% \n",
    "    inner_join(actor, by=c(actorid=\"id\")) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Alien cast list\n",
    "\n",
    "Obtain the cast list for the film 'Alien'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 7 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>John Hurt          </td></tr>\n",
       "\t<tr><td>Sigourney Weaver   </td></tr>\n",
       "\t<tr><td>Yaphet Kotto       </td></tr>\n",
       "\t<tr><td>Harry Dean Stanton </td></tr>\n",
       "\t<tr><td>Ian Holm           </td></tr>\n",
       "\t<tr><td>Tom Skerritt       </td></tr>\n",
       "\t<tr><td>Veronica Cartwright</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 7 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t John Hurt          \\\\\n",
       "\t Sigourney Weaver   \\\\\n",
       "\t Yaphet Kotto       \\\\\n",
       "\t Harry Dean Stanton \\\\\n",
       "\t Ian Holm           \\\\\n",
       "\t Tom Skerritt       \\\\\n",
       "\t Veronica Cartwright\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 7 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| John Hurt           |\n",
       "| Sigourney Weaver    |\n",
       "| Yaphet Kotto        |\n",
       "| Harry Dean Stanton  |\n",
       "| Ian Holm            |\n",
       "| Tom Skerritt        |\n",
       "| Veronica Cartwright |\n",
       "\n"
      ],
      "text/plain": [
       "  name               \n",
       "1 John Hurt          \n",
       "2 Sigourney Weaver   \n",
       "3 Yaphet Kotto       \n",
       "4 Harry Dean Stanton \n",
       "5 Ian Holm           \n",
       "6 Tom Skerritt       \n",
       "7 Veronica Cartwright"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "movie %>% \n",
    "    filter(title=='Alien') %>% \n",
    "    inner_join(casting, by=c(id=\"movieid\")) %>%\n",
    "    inner_join(actor, by=c(actorid=\"id\")) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Harrison Ford movies\n",
    "\n",
    "List the films in which 'Harrison Ford' has appeared"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 42 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>title</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A Hundred and One Nights     </td></tr>\n",
       "\t<tr><td>Air Force One                </td></tr>\n",
       "\t<tr><td>American Graffiti            </td></tr>\n",
       "\t<tr><td>Apocalypse Now               </td></tr>\n",
       "\t<tr><td>Clear and Present Danger     </td></tr>\n",
       "\t<tr><td><span style=white-space:pre-wrap>Cowboys &amp; Aliens             </span></td></tr>\n",
       "\t<tr><td>Crossing Over                </td></tr>\n",
       "\t<tr><td>Dead Heat on a Merry-Go-Round</td></tr>\n",
       "\t<tr><td>Extraordinary Measures       </td></tr>\n",
       "\t<tr><td>Firewall                     </td></tr>\n",
       "\t<tr><td>...</td></tr>\n",
       "\t<tr><td>Star Wars Episode V: The Empire Strikes Back</td></tr>\n",
       "\t<tr><td>Star Wars Episode VI: Return of the Jedi    </td></tr>\n",
       "\t<tr><td>The Conversation                            </td></tr>\n",
       "\t<tr><td>The Devil's Own                             </td></tr>\n",
       "\t<tr><td>The Fugitive                                </td></tr>\n",
       "\t<tr><td>The Mosquito Coast                          </td></tr>\n",
       "\t<tr><td>NA                                          </td></tr>\n",
       "\t<tr><td>What Lies Beneath                           </td></tr>\n",
       "\t<tr><td>Witness                                     </td></tr>\n",
       "\t<tr><td>Working Girl                                </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 42 × 1\n",
       "\\begin{tabular}{l}\n",
       " title\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t A Hundred and One Nights     \\\\\n",
       "\t Air Force One                \\\\\n",
       "\t American Graffiti            \\\\\n",
       "\t Apocalypse Now               \\\\\n",
       "\t Clear and Present Danger     \\\\\n",
       "\t Cowboys \\& Aliens             \\\\\n",
       "\t Crossing Over                \\\\\n",
       "\t Dead Heat on a Merry-Go-Round\\\\\n",
       "\t Extraordinary Measures       \\\\\n",
       "\t Firewall                     \\\\\n",
       "\t ...\\\\\n",
       "\t Star Wars Episode V: The Empire Strikes Back\\\\\n",
       "\t Star Wars Episode VI: Return of the Jedi    \\\\\n",
       "\t The Conversation                            \\\\\n",
       "\t The Devil's Own                             \\\\\n",
       "\t The Fugitive                                \\\\\n",
       "\t The Mosquito Coast                          \\\\\n",
       "\t NA                                          \\\\\n",
       "\t What Lies Beneath                           \\\\\n",
       "\t Witness                                     \\\\\n",
       "\t Working Girl                                \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 42 × 1\n",
       "\n",
       "| title &lt;chr&gt; |\n",
       "|---|\n",
       "| A Hundred and One Nights      |\n",
       "| Air Force One                 |\n",
       "| American Graffiti             |\n",
       "| Apocalypse Now                |\n",
       "| Clear and Present Danger      |\n",
       "| Cowboys &amp; Aliens              |\n",
       "| Crossing Over                 |\n",
       "| Dead Heat on a Merry-Go-Round |\n",
       "| Extraordinary Measures        |\n",
       "| Firewall                      |\n",
       "| ... |\n",
       "| Star Wars Episode V: The Empire Strikes Back |\n",
       "| Star Wars Episode VI: Return of the Jedi     |\n",
       "| The Conversation                             |\n",
       "| The Devil's Own                              |\n",
       "| The Fugitive                                 |\n",
       "| The Mosquito Coast                           |\n",
       "| NA                                           |\n",
       "| What Lies Beneath                            |\n",
       "| Witness                                      |\n",
       "| Working Girl                                 |\n",
       "\n"
      ],
      "text/plain": [
       "    title                                       \n",
       "1   A Hundred and One Nights                    \n",
       "2   Air Force One                               \n",
       "3   American Graffiti                           \n",
       "4   Apocalypse Now                              \n",
       "5   Clear and Present Danger                    \n",
       "6   Cowboys & Aliens                            \n",
       "7   Crossing Over                               \n",
       "8   Dead Heat on a Merry-Go-Round               \n",
       "9   Extraordinary Measures                      \n",
       "10  Firewall                                    \n",
       "... ...                                         \n",
       "33  Star Wars Episode V: The Empire Strikes Back\n",
       "34  Star Wars Episode VI: Return of the Jedi    \n",
       "35  The Conversation                            \n",
       "36  The Devil's Own                             \n",
       "37  The Fugitive                                \n",
       "38  The Mosquito Coast                          \n",
       "39  NA                                          \n",
       "40  What Lies Beneath                           \n",
       "41  Witness                                     \n",
       "42  Working Girl                                "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "actor %>% \n",
    "    filter(name=='Harrison Ford') %>%\n",
    "    inner_join(casting, by=c(id=\"actorid\")) %>%\n",
    "    left_join(movie, by=c(movieid=\"id\")) %>%\n",
    "    select(title)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Harrison Ford as a supporting actor\n",
    "\n",
    "List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 19 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>title</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A Hundred and One Nights                    </td></tr>\n",
       "\t<tr><td>American Graffiti                           </td></tr>\n",
       "\t<tr><td>Apocalypse Now                              </td></tr>\n",
       "\t<tr><td><span style=white-space:pre-wrap>Cowboys &amp; Aliens                            </span></td></tr>\n",
       "\t<tr><td>Dead Heat on a Merry-Go-Round               </td></tr>\n",
       "\t<tr><td>Extraordinary Measures                      </td></tr>\n",
       "\t<tr><td>Force 10 From Navarone                      </td></tr>\n",
       "\t<tr><td>Hawthorne of the U.S.A.                     </td></tr>\n",
       "\t<tr><td>Jimmy Hollywood                             </td></tr>\n",
       "\t<tr><td>More American Graffiti                      </td></tr>\n",
       "\t<tr><td>Morning Glory                               </td></tr>\n",
       "\t<tr><td>Sally of the Sawdust                        </td></tr>\n",
       "\t<tr><td>Shadows                                     </td></tr>\n",
       "\t<tr><td>Smilin' Through                             </td></tr>\n",
       "\t<tr><td>Star Wars Episode IV: A New Hope            </td></tr>\n",
       "\t<tr><td>Star Wars Episode V: The Empire Strikes Back</td></tr>\n",
       "\t<tr><td>Star Wars Episode VI: Return of the Jedi    </td></tr>\n",
       "\t<tr><td>The Conversation                            </td></tr>\n",
       "\t<tr><td>Working Girl                                </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 19 × 1\n",
       "\\begin{tabular}{l}\n",
       " title\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t A Hundred and One Nights                    \\\\\n",
       "\t American Graffiti                           \\\\\n",
       "\t Apocalypse Now                              \\\\\n",
       "\t Cowboys \\& Aliens                            \\\\\n",
       "\t Dead Heat on a Merry-Go-Round               \\\\\n",
       "\t Extraordinary Measures                      \\\\\n",
       "\t Force 10 From Navarone                      \\\\\n",
       "\t Hawthorne of the U.S.A.                     \\\\\n",
       "\t Jimmy Hollywood                             \\\\\n",
       "\t More American Graffiti                      \\\\\n",
       "\t Morning Glory                               \\\\\n",
       "\t Sally of the Sawdust                        \\\\\n",
       "\t Shadows                                     \\\\\n",
       "\t Smilin' Through                             \\\\\n",
       "\t Star Wars Episode IV: A New Hope            \\\\\n",
       "\t Star Wars Episode V: The Empire Strikes Back\\\\\n",
       "\t Star Wars Episode VI: Return of the Jedi    \\\\\n",
       "\t The Conversation                            \\\\\n",
       "\t Working Girl                                \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 19 × 1\n",
       "\n",
       "| title &lt;chr&gt; |\n",
       "|---|\n",
       "| A Hundred and One Nights                     |\n",
       "| American Graffiti                            |\n",
       "| Apocalypse Now                               |\n",
       "| Cowboys &amp; Aliens                             |\n",
       "| Dead Heat on a Merry-Go-Round                |\n",
       "| Extraordinary Measures                       |\n",
       "| Force 10 From Navarone                       |\n",
       "| Hawthorne of the U.S.A.                      |\n",
       "| Jimmy Hollywood                              |\n",
       "| More American Graffiti                       |\n",
       "| Morning Glory                                |\n",
       "| Sally of the Sawdust                         |\n",
       "| Shadows                                      |\n",
       "| Smilin' Through                              |\n",
       "| Star Wars Episode IV: A New Hope             |\n",
       "| Star Wars Episode V: The Empire Strikes Back |\n",
       "| Star Wars Episode VI: Return of the Jedi     |\n",
       "| The Conversation                             |\n",
       "| Working Girl                                 |\n",
       "\n"
      ],
      "text/plain": [
       "   title                                       \n",
       "1  A Hundred and One Nights                    \n",
       "2  American Graffiti                           \n",
       "3  Apocalypse Now                              \n",
       "4  Cowboys & Aliens                            \n",
       "5  Dead Heat on a Merry-Go-Round               \n",
       "6  Extraordinary Measures                      \n",
       "7  Force 10 From Navarone                      \n",
       "8  Hawthorne of the U.S.A.                     \n",
       "9  Jimmy Hollywood                             \n",
       "10 More American Graffiti                      \n",
       "11 Morning Glory                               \n",
       "12 Sally of the Sawdust                        \n",
       "13 Shadows                                     \n",
       "14 Smilin' Through                             \n",
       "15 Star Wars Episode IV: A New Hope            \n",
       "16 Star Wars Episode V: The Empire Strikes Back\n",
       "17 Star Wars Episode VI: Return of the Jedi    \n",
       "18 The Conversation                            \n",
       "19 Working Girl                                "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "actor %>% \n",
    "    filter(name=='Harrison Ford') %>%\n",
    "    inner_join(casting, by=c(id=\"actorid\")) %>%\n",
    "    filter(ord>1) %>%\n",
    "    left_join(movie, by=c(movieid=\"id\")) %>%\n",
    "    select(title)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Lead actors in 1962 movies\n",
    "\n",
    "List the films together with the leading star for all 1962 films."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 83 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>title</th><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A Kind of Loving                           </td><td>Alan Bates                </td></tr>\n",
       "\t<tr><td>A Symposium on Popular Songs               </td><td>Paul Frees                </td></tr>\n",
       "\t<tr><td>A Very Private Affair (Vie PrivÃ©e)        </td><td>Brigitte Bardot           </td></tr>\n",
       "\t<tr><td>An Autumn Afternoon                        </td><td>Chishu Ryu                </td></tr>\n",
       "\t<tr><td>Atraco a las tres                          </td><td>JosÃ© Luis LÃ³pez VÃ¡zquez</td></tr>\n",
       "\t<tr><td>Barabbas                                   </td><td>Anthony Quinn             </td></tr>\n",
       "\t<tr><td>Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)</td><td>Aleksandr Shvorin         </td></tr>\n",
       "\t<tr><td>Big and Little Wong Tin Bar                </td><td>Jackie Chan               </td></tr>\n",
       "\t<tr><td>Billy Budd                                 </td><td>Terence Stamp             </td></tr>\n",
       "\t<tr><td>Billy Rose's Jumbo                         </td><td>Doris Day                 </td></tr>\n",
       "\t<tr><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>The Trial                       </td><td>Anthony Perkins  </td></tr>\n",
       "\t<tr><td>Through a Glass Darkly          </td><td>Harriet Andersson</td></tr>\n",
       "\t<tr><td>To Kill a Mockingbird           </td><td>Gregory Peck     </td></tr>\n",
       "\t<tr><td>Tower of London                 </td><td>Vincent Price    </td></tr>\n",
       "\t<tr><td>Tutti a casa                    </td><td>Alberto Sordi    </td></tr>\n",
       "\t<tr><td>Two Half Times in Hell          </td><td>Imre Sinkovits   </td></tr>\n",
       "\t<tr><td>Varan the Unbelievable          </td><td>KÃ´zÃ´ Nomura    </td></tr>\n",
       "\t<tr><td>Village of Daughters            </td><td>Eric Sykes       </td></tr>\n",
       "\t<tr><td>What Ever Happened to Baby Jane?</td><td>Bette Davis      </td></tr>\n",
       "\t<tr><td>Who's Got the Action?           </td><td>Dean Martin      </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 83 × 2\n",
       "\\begin{tabular}{ll}\n",
       " title & name\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t A Kind of Loving                            & Alan Bates                \\\\\n",
       "\t A Symposium on Popular Songs                & Paul Frees                \\\\\n",
       "\t A Very Private Affair (Vie PrivÃ©e)         & Brigitte Bardot           \\\\\n",
       "\t An Autumn Afternoon                         & Chishu Ryu                \\\\\n",
       "\t Atraco a las tres                           & JosÃ© Luis LÃ³pez VÃ¡zquez\\\\\n",
       "\t Barabbas                                    & Anthony Quinn             \\\\\n",
       "\t Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚) & Aleksandr Shvorin         \\\\\n",
       "\t Big and Little Wong Tin Bar                 & Jackie Chan               \\\\\n",
       "\t Billy Budd                                  & Terence Stamp             \\\\\n",
       "\t Billy Rose's Jumbo                          & Doris Day                 \\\\\n",
       "\t ... & ...\\\\\n",
       "\t The Trial                        & Anthony Perkins  \\\\\n",
       "\t Through a Glass Darkly           & Harriet Andersson\\\\\n",
       "\t To Kill a Mockingbird            & Gregory Peck     \\\\\n",
       "\t Tower of London                  & Vincent Price    \\\\\n",
       "\t Tutti a casa                     & Alberto Sordi    \\\\\n",
       "\t Two Half Times in Hell           & Imre Sinkovits   \\\\\n",
       "\t Varan the Unbelievable           & KÃ´zÃ´ Nomura    \\\\\n",
       "\t Village of Daughters             & Eric Sykes       \\\\\n",
       "\t What Ever Happened to Baby Jane? & Bette Davis      \\\\\n",
       "\t Who's Got the Action?            & Dean Martin      \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 83 × 2\n",
       "\n",
       "| title &lt;chr&gt; | name &lt;chr&gt; |\n",
       "|---|---|\n",
       "| A Kind of Loving                            | Alan Bates                 |\n",
       "| A Symposium on Popular Songs                | Paul Frees                 |\n",
       "| A Very Private Affair (Vie PrivÃ©e)         | Brigitte Bardot            |\n",
       "| An Autumn Afternoon                         | Chishu Ryu                 |\n",
       "| Atraco a las tres                           | JosÃ© Luis LÃ³pez VÃ¡zquez |\n",
       "| Barabbas                                    | Anthony Quinn              |\n",
       "| Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚) | Aleksandr Shvorin          |\n",
       "| Big and Little Wong Tin Bar                 | Jackie Chan                |\n",
       "| Billy Budd                                  | Terence Stamp              |\n",
       "| Billy Rose's Jumbo                          | Doris Day                  |\n",
       "| ... | ... |\n",
       "| The Trial                        | Anthony Perkins   |\n",
       "| Through a Glass Darkly           | Harriet Andersson |\n",
       "| To Kill a Mockingbird            | Gregory Peck      |\n",
       "| Tower of London                  | Vincent Price     |\n",
       "| Tutti a casa                     | Alberto Sordi     |\n",
       "| Two Half Times in Hell           | Imre Sinkovits    |\n",
       "| Varan the Unbelievable           | KÃ´zÃ´ Nomura     |\n",
       "| Village of Daughters             | Eric Sykes        |\n",
       "| What Ever Happened to Baby Jane? | Bette Davis       |\n",
       "| Who's Got the Action?            | Dean Martin       |\n",
       "\n"
      ],
      "text/plain": [
       "    title                                       name                      \n",
       "1   A Kind of Loving                            Alan Bates                \n",
       "2   A Symposium on Popular Songs                Paul Frees                \n",
       "3   A Very Private Affair (Vie PrivÃ©e)         Brigitte Bardot           \n",
       "4   An Autumn Afternoon                         Chishu Ryu                \n",
       "5   Atraco a las tres                           JosÃ© Luis LÃ³pez VÃ¡zquez\n",
       "6   Barabbas                                    Anthony Quinn             \n",
       "7   Battle Beyond the Sun (Ð\\235ÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚) Aleksandr Shvorin         \n",
       "8   Big and Little Wong Tin Bar                 Jackie Chan               \n",
       "9   Billy Budd                                  Terence Stamp             \n",
       "10  Billy Rose's Jumbo                          Doris Day                 \n",
       "... ...                                         ...                       \n",
       "74  The Trial                                   Anthony Perkins           \n",
       "75  Through a Glass Darkly                      Harriet Andersson         \n",
       "76  To Kill a Mockingbird                       Gregory Peck              \n",
       "77  Tower of London                             Vincent Price             \n",
       "78  Tutti a casa                                Alberto Sordi             \n",
       "79  Two Half Times in Hell                      Imre Sinkovits            \n",
       "80  Varan the Unbelievable                      KÃ´zÃ´ Nomura             \n",
       "81  Village of Daughters                        Eric Sykes                \n",
       "82  What Ever Happened to Baby Jane?            Bette Davis               \n",
       "83  Who's Got the Action?                       Dean Martin               "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "actor %>%\n",
    "    inner_join(casting, by=c(id=\"actorid\")) %>%\n",
    "    filter(ord==1) %>%\n",
    "    left_join(movie, by=c(movieid=\"id\")) %>%\n",
    "    filter(yr==1962) %>%\n",
    "    select(title, name) %>%\n",
    "    arrange(title)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Busy years for Rock Hudson\n",
    "\n",
    "Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 2 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>n</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1953</td><td>5</td></tr>\n",
       "\t<tr><td>1961</td><td>3</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 2 × 2\n",
       "\\begin{tabular}{ll}\n",
       " yr & n\\\\\n",
       " <int> & <int>\\\\\n",
       "\\hline\n",
       "\t 1953 & 5\\\\\n",
       "\t 1961 & 3\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 2 × 2\n",
       "\n",
       "| yr &lt;int&gt; | n &lt;int&gt; |\n",
       "|---|---|\n",
       "| 1953 | 5 |\n",
       "| 1961 | 3 |\n",
       "\n"
      ],
      "text/plain": [
       "  yr   n\n",
       "1 1953 5\n",
       "2 1961 3"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "actor %>%\n",
    "    filter(name=='Rock Hudson') %>%\n",
    "    inner_join(casting, by=c(id=\"actorid\")) %>%\n",
    "    inner_join(movie, by=c(movieid=\"id\")) %>%\n",
    "    group_by(yr) %>% \n",
    "    summarise(n=n()) %>%\n",
    "    filter(n>2) %>% \n",
    "    arrange(desc(n))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Lead actor in Julie Andrews movies\n",
    "\n",
    "List the film title and the leading actor for all of the films 'Julie Andrews' played in.\n",
    "\n",
    "> _Did you get \"Little Miss Marker twice\"?_   \n",
    "> Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).\n",
    ">\n",
    "> Title is not a unique field, create a table of IDs in your subquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 19 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>title</th><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>10                                      </td><td>Dudley Moore  </td></tr>\n",
       "\t<tr><td>Darling Lili                            </td><td>Julie Andrews </td></tr>\n",
       "\t<tr><td>Despicable Me                           </td><td>Steve Carell  </td></tr>\n",
       "\t<tr><td>Duet for One                            </td><td>Julie Andrews </td></tr>\n",
       "\t<tr><td>Hawaii                                  </td><td>Julie Andrews </td></tr>\n",
       "\t<tr><td>Little Miss Marker                      </td><td>Walter Matthau</td></tr>\n",
       "\t<tr><td>Mary Poppins                            </td><td>Julie Andrews </td></tr>\n",
       "\t<tr><td>Relative Values                         </td><td>Julie Andrews </td></tr>\n",
       "\t<tr><td>Shrek the Third                         </td><td>Mike Myers    </td></tr>\n",
       "\t<tr><td>Star!                                   </td><td>Julie Andrews </td></tr>\n",
       "\t<tr><td>The Americanization of Emily            </td><td>James Garner  </td></tr>\n",
       "\t<tr><td>The Pink Panther Strikes Again          </td><td>Peter Sellers </td></tr>\n",
       "\t<tr><td>The Princess Diaries                    </td><td>Anne Hathaway </td></tr>\n",
       "\t<tr><td>The Princess Diaries 2: Royal Engagement</td><td>Anne Hathaway </td></tr>\n",
       "\t<tr><td>The Tamarind Seed                       </td><td>Julie Andrews </td></tr>\n",
       "\t<tr><td>Thoroughly Modern Millie                </td><td>Julie Andrews </td></tr>\n",
       "\t<tr><td>Tooth Fairy                             </td><td>Dwayne Johnson</td></tr>\n",
       "\t<tr><td>Torn Curtain                            </td><td>Paul Newman   </td></tr>\n",
       "\t<tr><td>Victor Victoria                         </td><td>Julie Andrews </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 19 × 2\n",
       "\\begin{tabular}{ll}\n",
       " title & name\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 10                                       & Dudley Moore  \\\\\n",
       "\t Darling Lili                             & Julie Andrews \\\\\n",
       "\t Despicable Me                            & Steve Carell  \\\\\n",
       "\t Duet for One                             & Julie Andrews \\\\\n",
       "\t Hawaii                                   & Julie Andrews \\\\\n",
       "\t Little Miss Marker                       & Walter Matthau\\\\\n",
       "\t Mary Poppins                             & Julie Andrews \\\\\n",
       "\t Relative Values                          & Julie Andrews \\\\\n",
       "\t Shrek the Third                          & Mike Myers    \\\\\n",
       "\t Star!                                    & Julie Andrews \\\\\n",
       "\t The Americanization of Emily             & James Garner  \\\\\n",
       "\t The Pink Panther Strikes Again           & Peter Sellers \\\\\n",
       "\t The Princess Diaries                     & Anne Hathaway \\\\\n",
       "\t The Princess Diaries 2: Royal Engagement & Anne Hathaway \\\\\n",
       "\t The Tamarind Seed                        & Julie Andrews \\\\\n",
       "\t Thoroughly Modern Millie                 & Julie Andrews \\\\\n",
       "\t Tooth Fairy                              & Dwayne Johnson\\\\\n",
       "\t Torn Curtain                             & Paul Newman   \\\\\n",
       "\t Victor Victoria                          & Julie Andrews \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 19 × 2\n",
       "\n",
       "| title &lt;chr&gt; | name &lt;chr&gt; |\n",
       "|---|---|\n",
       "| 10                                       | Dudley Moore   |\n",
       "| Darling Lili                             | Julie Andrews  |\n",
       "| Despicable Me                            | Steve Carell   |\n",
       "| Duet for One                             | Julie Andrews  |\n",
       "| Hawaii                                   | Julie Andrews  |\n",
       "| Little Miss Marker                       | Walter Matthau |\n",
       "| Mary Poppins                             | Julie Andrews  |\n",
       "| Relative Values                          | Julie Andrews  |\n",
       "| Shrek the Third                          | Mike Myers     |\n",
       "| Star!                                    | Julie Andrews  |\n",
       "| The Americanization of Emily             | James Garner   |\n",
       "| The Pink Panther Strikes Again           | Peter Sellers  |\n",
       "| The Princess Diaries                     | Anne Hathaway  |\n",
       "| The Princess Diaries 2: Royal Engagement | Anne Hathaway  |\n",
       "| The Tamarind Seed                        | Julie Andrews  |\n",
       "| Thoroughly Modern Millie                 | Julie Andrews  |\n",
       "| Tooth Fairy                              | Dwayne Johnson |\n",
       "| Torn Curtain                             | Paul Newman    |\n",
       "| Victor Victoria                          | Julie Andrews  |\n",
       "\n"
      ],
      "text/plain": [
       "   title                                    name          \n",
       "1  10                                       Dudley Moore  \n",
       "2  Darling Lili                             Julie Andrews \n",
       "3  Despicable Me                            Steve Carell  \n",
       "4  Duet for One                             Julie Andrews \n",
       "5  Hawaii                                   Julie Andrews \n",
       "6  Little Miss Marker                       Walter Matthau\n",
       "7  Mary Poppins                             Julie Andrews \n",
       "8  Relative Values                          Julie Andrews \n",
       "9  Shrek the Third                          Mike Myers    \n",
       "10 Star!                                    Julie Andrews \n",
       "11 The Americanization of Emily             James Garner  \n",
       "12 The Pink Panther Strikes Again           Peter Sellers \n",
       "13 The Princess Diaries                     Anne Hathaway \n",
       "14 The Princess Diaries 2: Royal Engagement Anne Hathaway \n",
       "15 The Tamarind Seed                        Julie Andrews \n",
       "16 Thoroughly Modern Millie                 Julie Andrews \n",
       "17 Tooth Fairy                              Dwayne Johnson\n",
       "18 Torn Curtain                             Paul Newman   \n",
       "19 Victor Victoria                          Julie Andrews "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "movie %>% \n",
    "    inner_join(casting, by=c(id=\"movieid\")) %>%\n",
    "    inner_join(actor, by=c(actorid=\"id\")) %>%\n",
    "    filter(ord==1 & id %in% (\n",
    "        movie %>% \n",
    "        inner_join(casting, by=c(id=\"movieid\")) %>%\n",
    "        inner_join(actor, by=c(actorid=\"id\")) %>%\n",
    "        filter(name=='Julie Andrews') %>% \n",
    "        select(id) %>% \n",
    "        pull)\n",
    "    ) %>% \n",
    "    select(title, name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. Actors with 15 leading roles\n",
    "\n",
    "Obtain a list, in alphabetical order, of actors who've had at least 15 **starring** roles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 100 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Adam Sandler         </td></tr>\n",
       "\t<tr><td>Al Pacino            </td></tr>\n",
       "\t<tr><td>Anthony Hopkins      </td></tr>\n",
       "\t<tr><td>Antonio Banderas     </td></tr>\n",
       "\t<tr><td>Arnold Schwarzenegger</td></tr>\n",
       "\t<tr><td>Barbara Stanwyck     </td></tr>\n",
       "\t<tr><td>Basil Rathbone       </td></tr>\n",
       "\t<tr><td>Ben Affleck          </td></tr>\n",
       "\t<tr><td>Bette Davis          </td></tr>\n",
       "\t<tr><td>Betty Grable         </td></tr>\n",
       "\t<tr><td>...</td></tr>\n",
       "\t<tr><td>Steve Martin      </td></tr>\n",
       "\t<tr><td>Sylvester Stallone</td></tr>\n",
       "\t<tr><td>Tom Berenger      </td></tr>\n",
       "\t<tr><td>Tom Cruise        </td></tr>\n",
       "\t<tr><td>Tom Hanks         </td></tr>\n",
       "\t<tr><td>Tommy Lee Jones   </td></tr>\n",
       "\t<tr><td>Tyrone Power      </td></tr>\n",
       "\t<tr><td>Walter Matthau    </td></tr>\n",
       "\t<tr><td>William Garwood   </td></tr>\n",
       "\t<tr><td>William Holden    </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 100 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Adam Sandler         \\\\\n",
       "\t Al Pacino            \\\\\n",
       "\t Anthony Hopkins      \\\\\n",
       "\t Antonio Banderas     \\\\\n",
       "\t Arnold Schwarzenegger\\\\\n",
       "\t Barbara Stanwyck     \\\\\n",
       "\t Basil Rathbone       \\\\\n",
       "\t Ben Affleck          \\\\\n",
       "\t Bette Davis          \\\\\n",
       "\t Betty Grable         \\\\\n",
       "\t ...\\\\\n",
       "\t Steve Martin      \\\\\n",
       "\t Sylvester Stallone\\\\\n",
       "\t Tom Berenger      \\\\\n",
       "\t Tom Cruise        \\\\\n",
       "\t Tom Hanks         \\\\\n",
       "\t Tommy Lee Jones   \\\\\n",
       "\t Tyrone Power      \\\\\n",
       "\t Walter Matthau    \\\\\n",
       "\t William Garwood   \\\\\n",
       "\t William Holden    \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 100 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Adam Sandler          |\n",
       "| Al Pacino             |\n",
       "| Anthony Hopkins       |\n",
       "| Antonio Banderas      |\n",
       "| Arnold Schwarzenegger |\n",
       "| Barbara Stanwyck      |\n",
       "| Basil Rathbone        |\n",
       "| Ben Affleck           |\n",
       "| Bette Davis           |\n",
       "| Betty Grable          |\n",
       "| ... |\n",
       "| Steve Martin       |\n",
       "| Sylvester Stallone |\n",
       "| Tom Berenger       |\n",
       "| Tom Cruise         |\n",
       "| Tom Hanks          |\n",
       "| Tommy Lee Jones    |\n",
       "| Tyrone Power       |\n",
       "| Walter Matthau     |\n",
       "| William Garwood    |\n",
       "| William Holden     |\n",
       "\n"
      ],
      "text/plain": [
       "    name                 \n",
       "1   Adam Sandler         \n",
       "2   Al Pacino            \n",
       "3   Anthony Hopkins      \n",
       "4   Antonio Banderas     \n",
       "5   Arnold Schwarzenegger\n",
       "6   Barbara Stanwyck     \n",
       "7   Basil Rathbone       \n",
       "8   Ben Affleck          \n",
       "9   Bette Davis          \n",
       "10  Betty Grable         \n",
       "... ...                  \n",
       "91  Steve Martin         \n",
       "92  Sylvester Stallone   \n",
       "93  Tom Berenger         \n",
       "94  Tom Cruise           \n",
       "95  Tom Hanks            \n",
       "96  Tommy Lee Jones      \n",
       "97  Tyrone Power         \n",
       "98  Walter Matthau       \n",
       "99  William Garwood      \n",
       "100 William Holden       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "casting %>% \n",
    "    filter(ord==1) %>% \n",
    "    group_by(actorid) %>% \n",
    "    summarise(n=n()) %>%\n",
    "    filter(n>=15) %>% \n",
    "    inner_join(actor, by=c(actorid=\"id\")) %>%\n",
    "    select(name) %>%\n",
    "    arrange(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "List the films released in the year 1978 ordered by the number of actors in the cast, then by title."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 104 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>title</th><th scope=col>actors</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A Night Full of Rain                    </td><td>19</td></tr>\n",
       "\t<tr><td>A Wedding                               </td><td>19</td></tr>\n",
       "\t<tr><td>Alexandria... Why?                      </td><td>15</td></tr>\n",
       "\t<tr><td>Almost Summer                           </td><td>13</td></tr>\n",
       "\t<tr><td>American Boy: A Profile of Steven Prince</td><td> 3</td></tr>\n",
       "\t<tr><td>American Hot Wax                        </td><td>27</td></tr>\n",
       "\t<tr><td>An Unmarried Woman                      </td><td>13</td></tr>\n",
       "\t<tr><td>Attack of the Killer Tomatoes           </td><td> 7</td></tr>\n",
       "\t<tr><td>Autumn Sonata                           </td><td>11</td></tr>\n",
       "\t<tr><td>Big Wednesday                           </td><td>21</td></tr>\n",
       "\t<tr><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>The Swarm           </td><td>37</td></tr>\n",
       "\t<tr><td>The Water Babies    </td><td>15</td></tr>\n",
       "\t<tr><td>The Wiz             </td><td> 9</td></tr>\n",
       "\t<tr><td>Up in Smoke         </td><td>11</td></tr>\n",
       "\t<tr><td>Violette NoziÃ¨re   </td><td>15</td></tr>\n",
       "\t<tr><td>Watership Down      </td><td> 6</td></tr>\n",
       "\t<tr><td>Who'll Stop The Rain</td><td>15</td></tr>\n",
       "\t<tr><td>Without Anesthesia  </td><td>15</td></tr>\n",
       "\t<tr><td>You Are Not Alone   </td><td> 5</td></tr>\n",
       "\t<tr><td>Zero to Sixty       </td><td>10</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 104 × 2\n",
       "\\begin{tabular}{ll}\n",
       " title & actors\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t A Night Full of Rain                     & 19\\\\\n",
       "\t A Wedding                                & 19\\\\\n",
       "\t Alexandria... Why?                       & 15\\\\\n",
       "\t Almost Summer                            & 13\\\\\n",
       "\t American Boy: A Profile of Steven Prince &  3\\\\\n",
       "\t American Hot Wax                         & 27\\\\\n",
       "\t An Unmarried Woman                       & 13\\\\\n",
       "\t Attack of the Killer Tomatoes            &  7\\\\\n",
       "\t Autumn Sonata                            & 11\\\\\n",
       "\t Big Wednesday                            & 21\\\\\n",
       "\t ... & ...\\\\\n",
       "\t The Swarm            & 37\\\\\n",
       "\t The Water Babies     & 15\\\\\n",
       "\t The Wiz              &  9\\\\\n",
       "\t Up in Smoke          & 11\\\\\n",
       "\t Violette NoziÃ¨re    & 15\\\\\n",
       "\t Watership Down       &  6\\\\\n",
       "\t Who'll Stop The Rain & 15\\\\\n",
       "\t Without Anesthesia   & 15\\\\\n",
       "\t You Are Not Alone    &  5\\\\\n",
       "\t Zero to Sixty        & 10\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 104 × 2\n",
       "\n",
       "| title &lt;chr&gt; | actors &lt;int&gt; |\n",
       "|---|---|\n",
       "| A Night Full of Rain                     | 19 |\n",
       "| A Wedding                                | 19 |\n",
       "| Alexandria... Why?                       | 15 |\n",
       "| Almost Summer                            | 13 |\n",
       "| American Boy: A Profile of Steven Prince |  3 |\n",
       "| American Hot Wax                         | 27 |\n",
       "| An Unmarried Woman                       | 13 |\n",
       "| Attack of the Killer Tomatoes            |  7 |\n",
       "| Autumn Sonata                            | 11 |\n",
       "| Big Wednesday                            | 21 |\n",
       "| ... | ... |\n",
       "| The Swarm            | 37 |\n",
       "| The Water Babies     | 15 |\n",
       "| The Wiz              |  9 |\n",
       "| Up in Smoke          | 11 |\n",
       "| Violette NoziÃ¨re    | 15 |\n",
       "| Watership Down       |  6 |\n",
       "| Who'll Stop The Rain | 15 |\n",
       "| Without Anesthesia   | 15 |\n",
       "| You Are Not Alone    |  5 |\n",
       "| Zero to Sixty        | 10 |\n",
       "\n"
      ],
      "text/plain": [
       "    title                                    actors\n",
       "1   A Night Full of Rain                     19    \n",
       "2   A Wedding                                19    \n",
       "3   Alexandria... Why?                       15    \n",
       "4   Almost Summer                            13    \n",
       "5   American Boy: A Profile of Steven Prince  3    \n",
       "6   American Hot Wax                         27    \n",
       "7   An Unmarried Woman                       13    \n",
       "8   Attack of the Killer Tomatoes             7    \n",
       "9   Autumn Sonata                            11    \n",
       "10  Big Wednesday                            21    \n",
       "... ...                                      ...   \n",
       "95  The Swarm                                37    \n",
       "96  The Water Babies                         15    \n",
       "97  The Wiz                                   9    \n",
       "98  Up in Smoke                              11    \n",
       "99  Violette NoziÃ¨re                        15    \n",
       "100 Watership Down                            6    \n",
       "101 Who'll Stop The Rain                     15    \n",
       "102 Without Anesthesia                       15    \n",
       "103 You Are Not Alone                         5    \n",
       "104 Zero to Sixty                            10    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "movie %>% \n",
    "    filter(yr==1978) %>% \n",
    "    inner_join(casting, by=c(id=\"movieid\")) %>%\n",
    "    group_by(id, title) %>% \n",
    "    summarise(actors=n()) %>%\n",
    "    ungroup() %>%\n",
    "    select(title, actors) %>% \n",
    "    arrange(title)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "\n",
    "List all the people who have worked with 'Art Garfunkel'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 36 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Beverly Johnson</td></tr>\n",
       "\t<tr><td>Elio Fiorucci  </td></tr>\n",
       "\t<tr><td>Robert DoQui   </td></tr>\n",
       "\t<tr><td>Donald Trump   </td></tr>\n",
       "\t<tr><td>Michael York   </td></tr>\n",
       "\t<tr><td>Ron Jeremy     </td></tr>\n",
       "\t<tr><td>Cindy Crawford </td></tr>\n",
       "\t<tr><td>Heidi Klum     </td></tr>\n",
       "\t<tr><td>Mark Ruffalo   </td></tr>\n",
       "\t<tr><td>Neve Campbell  </td></tr>\n",
       "\t<tr><td>...</td></tr>\n",
       "\t<tr><td>Breckin Meyer         </td></tr>\n",
       "\t<tr><td>Valerie Perrine       </td></tr>\n",
       "\t<tr><td>Bill Paxton           </td></tr>\n",
       "\t<tr><td>Lorna Luft            </td></tr>\n",
       "\t<tr><td>Heather Matarazzo     </td></tr>\n",
       "\t<tr><td>Bruce Jay Friedman    </td></tr>\n",
       "\t<tr><td>Skipp Sudduth         </td></tr>\n",
       "\t<tr><td>Georgina Grenville    </td></tr>\n",
       "\t<tr><td>Sherilyn Fenn         </td></tr>\n",
       "\t<tr><td>Frederique van der Wal</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 36 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Beverly Johnson\\\\\n",
       "\t Elio Fiorucci  \\\\\n",
       "\t Robert DoQui   \\\\\n",
       "\t Donald Trump   \\\\\n",
       "\t Michael York   \\\\\n",
       "\t Ron Jeremy     \\\\\n",
       "\t Cindy Crawford \\\\\n",
       "\t Heidi Klum     \\\\\n",
       "\t Mark Ruffalo   \\\\\n",
       "\t Neve Campbell  \\\\\n",
       "\t ...\\\\\n",
       "\t Breckin Meyer         \\\\\n",
       "\t Valerie Perrine       \\\\\n",
       "\t Bill Paxton           \\\\\n",
       "\t Lorna Luft            \\\\\n",
       "\t Heather Matarazzo     \\\\\n",
       "\t Bruce Jay Friedman    \\\\\n",
       "\t Skipp Sudduth         \\\\\n",
       "\t Georgina Grenville    \\\\\n",
       "\t Sherilyn Fenn         \\\\\n",
       "\t Frederique van der Wal\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 36 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Beverly Johnson |\n",
       "| Elio Fiorucci   |\n",
       "| Robert DoQui    |\n",
       "| Donald Trump    |\n",
       "| Michael York    |\n",
       "| Ron Jeremy      |\n",
       "| Cindy Crawford  |\n",
       "| Heidi Klum      |\n",
       "| Mark Ruffalo    |\n",
       "| Neve Campbell   |\n",
       "| ... |\n",
       "| Breckin Meyer          |\n",
       "| Valerie Perrine        |\n",
       "| Bill Paxton            |\n",
       "| Lorna Luft             |\n",
       "| Heather Matarazzo      |\n",
       "| Bruce Jay Friedman     |\n",
       "| Skipp Sudduth          |\n",
       "| Georgina Grenville     |\n",
       "| Sherilyn Fenn          |\n",
       "| Frederique van der Wal |\n",
       "\n"
      ],
      "text/plain": [
       "    name                  \n",
       "1   Beverly Johnson       \n",
       "2   Elio Fiorucci         \n",
       "3   Robert DoQui          \n",
       "4   Donald Trump          \n",
       "5   Michael York          \n",
       "6   Ron Jeremy            \n",
       "7   Cindy Crawford        \n",
       "8   Heidi Klum            \n",
       "9   Mark Ruffalo          \n",
       "10  Neve Campbell         \n",
       "... ...                   \n",
       "27  Breckin Meyer         \n",
       "28  Valerie Perrine       \n",
       "29  Bill Paxton           \n",
       "30  Lorna Luft            \n",
       "31  Heather Matarazzo     \n",
       "32  Bruce Jay Friedman    \n",
       "33  Skipp Sudduth         \n",
       "34  Georgina Grenville    \n",
       "35  Sherilyn Fenn         \n",
       "36  Frederique van der Wal"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "actor %>%\n",
    "    inner_join(casting, by=c(id=\"actorid\")) %>%\n",
    "    filter(movieid %in% (\n",
    "        actor %>% \n",
    "        inner_join(casting, by=c(id=\"actorid\")) %>%\n",
    "        filter(name=='Art Garfunkel') %>% \n",
    "        select(movieid) %>%\n",
    "        pull)\n",
    "    ) %>% \n",
    "    filter(name != 'Art Garfunkel') %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.3.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
